import sys
import os
import sqlite3

# 添加项目根目录到Python路径
backend_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, backend_path)

from database import database
# 从当前目录导入modify模块
from .modify import modify_excel_file

def get_fault_details(page: int = 1, page_size: int = 7):
    """
    获取故障明细数据（支持分页）
    该函数从数据库中查询所有故障明细记录，并将结果转换为字典列表格式返回。
    
    Args:
        page: 页码，从1开始
        page_size: 每页记录数，默认7条
    
    Returns:
        dict: 包含数据列表和分页信息的字典
    """
    try:
        # 计算偏移量
        offset = (page - 1) * page_size
        
        # 建立数据库连接
        conn = database.get_db_connection()
        # 创建游标对象用于执行SQL语句
        cursor = conn.cursor()
        # 执行查询语句，只获取前端需要的字段（分页）
        cursor.execute("""
            SELECT id, serial_number, device_name, resource_pool, phase, 
                   fault_time, alarm_clear_time, device_category, manufacturer, 
                   component_type, discovery_method, solution_record, 
                   is_domestic, component_sn 
            FROM fault_details 
            LIMIT ? OFFSET ?
        """, (page_size, offset))
        # 获取查询结果的所有行
        faults = cursor.fetchall()
        
        # 获取总记录数
        cursor.execute("SELECT COUNT(*) FROM fault_details")
        total = cursor.fetchone()[0]
        
        # 关闭数据库连接
        conn.close()
        
        # 将每条记录转换为字典格式，并组成列表返回
        # 这样做是为了让数据更易于处理和序列化
        result = [dict(fault) for fault in faults]
        print(f"从数据库获取到 {len(result)} 条故障明细数据（第{page}页）")
        
        return {
            "data": result,
            "pagination": {
                "page": page,
                "page_size": page_size,
                "total": total,
                "pages": (total + page_size - 1) // page_size  # 向上取整计算总页数
            }
        }
    except Exception as e:
        print(f"获取故障明细数据时出错: {str(e)}")
        return {
            "data": [],
            "pagination": {
                "page": page,
                "page_size": page_size,
                "total": 0,
                "pages": 0
            }
        }

def get_fault_counts(page: int = 1, page_size: int = 10):
    """
    获取故障数量统计（支持分页）
    
    Args:
        page: 页码，从1开始
        page_size: 每页记录数，默认10条
    
    Returns:
        dict: 包含数据列表和分页信息的字典
    """
    try:
        # 计算偏移量
        offset = (page - 1) * page_size
        
        conn = database.get_db_connection()
        cursor = conn.cursor()
        # 执行查询语句，只获取前端需要的字段（分页）
        cursor.execute("""
            SELECT id, phase, hardware_resource_pool, software_manufacturer, 
                   fault_handling_count, created_at 
            FROM fault_counts 
            LIMIT ? OFFSET ?
        """, (page_size, offset))
        counts = cursor.fetchall()
        
        # 获取总记录数
        cursor.execute("SELECT COUNT(*) FROM fault_counts")
        total = cursor.fetchone()[0]
        
        conn.close()
        
        # 确保返回所有记录，包括故障处理次数为0的记录
        result = [dict(count) for count in counts]
        
        return {
            "data": result,
            "pagination": {
                "page": page,
                "page_size": page_size,
                "total": total,
                "pages": (total + page_size - 1) // page_size
            }
        }
    except Exception as e:
        print(f"获取故障数量统计时出错: {str(e)}")
        return {
            "data": [],
            "pagination": {
                "page": page,
                "page_size": page_size,
                "total": 0,
                "pages": 0
            }
        }

def get_manufacturer_stats(page: int = 1, page_size: int = 5):
    """
    获取厂家故障统计（支持分页）
    
    Args:
        page: 页码，从1开始
        page_size: 每页记录数，默认5条
    
    Returns:
        dict: 包含数据列表和分页信息的字典
    """
    try:
        # 计算偏移量
        offset = (page - 1) * page_size
        
        conn = database.get_db_connection()
        # 设置行工厂以确保列名正确映射
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        # 执行查询语句，只获取前端需要的字段（分页）
        cursor.execute("""
            SELECT id, manufacturer, device_type, optical_module, cpu_non_domestic, 
                   cpu_domestic_hygon, cpu_domestic_arm, memory, disk_non_domestic, 
                   disk_domestic, cache_disk, backplane, nic_non_domestic, nic_domestic, 
                   sensor, riser_card, pcie_card_non_domestic, pcie_card_domestic, 
                   motherboard, power_supply, raid_card_non_domestic, raid_card_domestic, 
                   fiber, fan_non_domestic, fan_domestic, whole_machine, total_count, 
                   created_at 
            FROM manufacturer_stats 
            LIMIT ? OFFSET ?
        """, (page_size, offset))
        stats = cursor.fetchall()
        
        # 获取总记录数
        cursor.execute("SELECT COUNT(*) FROM manufacturer_stats")
        total = cursor.fetchone()[0]
        
        conn.close()
        
        # 转换为字典列表，确保包含所有字段
        result = []
        for stat in stats:
            stat_dict = dict(stat)
            # 确保主板和电源字段存在
            result.append(stat_dict)
        
        return {
            "data": result,
            "pagination": {
                "page": page,
                "page_size": page_size,
                "total": total,
                "pages": (total + page_size - 1) // page_size
            }
        }
    except Exception as e:
        print(f"获取厂家故障统计时出错: {str(e)}")
        return {
            "data": [],
            "pagination": {
                "page": page,
                "page_size": page_size,
                "total": 0,
                "pages": 0
            }
        }

def get_fault_description():
    """获取详细故障描述"""
    try:
        conn = database.get_db_connection()
        conn.row_factory = sqlite3.Row  # 确保可以使用列名访问
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM fault_descriptions ORDER BY id DESC LIMIT 1")
        description = cursor.fetchone()
        conn.close()
        
        if description:
            return {"id": description["id"], "description": description["description"]}
        return {"id": 0, "description": "暂无详细故障描述"}
    except Exception as e:
        print(f"获取详细故障描述时出错: {str(e)}")
        import traceback
        traceback.print_exc()
        return {"id": 0, "description": "获取详细故障描述失败"}

def process_fault_data():
    """处理故障数据"""
    try:
        # 初始化数据库
        database.init_database()
        
        # 使用动态生成的文件名
        import datetime
        project_root = os.path.dirname(backend_path)
        
        # 获取当前日期
        now = datetime.datetime.now()
        current_month = now.month
        current_year = now.year


        #月份不减一，确保使用当前月份的文件
        current_month = current_month
        current_year = current_year
        
        '''
        # 月份减一，确保使用上个月的文件
        current_month = current_month - 1
        # 处理1月减一的情况（变为上一年的12月）
        if current_month == 0:
            current_month = 12
            # 年份也需要减一
            current_year = current_year - 1'''
        
        # 根据项目规范，使用不带"_副本"后缀的文件名
        file_path = os.path.join(project_root, f"网络云运维日报故障处理上报{current_year}年{current_month:02d}月-陕西省.xlsx")
        
        if os.path.exists(file_path):
            # 调用modify_excel_file函数处理Excel文件
            result = modify_excel_file(file_path)
            
            return {"status": "success", "result": result}
        else:
            return {"status": "error", "message": f"文件 {file_path} 不存在"}
    except Exception as e:
        return {"status": "error", "message": str(e)}